﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADOX;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.OleDb;

namespace DBconnection
{
    public static class DB
    {
        private static OleDbConnection con = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = DBSimulation.mdb");   // create connection
        private static OleDbCommand com;  // create command

        public static void Create()
        {
            try
            {
                Catalog cat = new Catalog();
                string strCreateDB = "";
                strCreateDB = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = DBSimulation.mdb";
                cat.Create(strCreateDB);
                OpenConnection();
                com = new OleDbCommand("CREATE TABLE Classes (Eng_Name varchar(255),Heb_Name varchar(255),[Desc] varchar(255), [Type] varchar(255), Struct_Name varchar(255))", con);
                com.ExecuteNonQuery();
                com = new OleDbCommand("CREATE TABLE Rules (ID_Rule varchar(4),Text_Rule varchar(255),Contax_Rule varchar(255))", con);
                com.ExecuteNonQuery();
                CloseConnection();
            }
            catch
            {
                
            }
        }

        public static void OpenConnection()
        {
            try
            {
                con.Open();
            }
            catch
            {
               // Console.WriteLine("Connection already Open");
                con.Close();
                con.Open();
            }
        }

        public static void CloseConnection()
        {
            try
            {
                con.Close();
            }
            catch
            {
                Console.WriteLine("Connection already Closed");
            }
        }

        public static bool SearchInDb(string word)
        {
            com = new OleDbCommand("SELECT COUNT(Eng_Name) FROM Classes WHERE Eng_Name=@Name", con);
            com.Parameters.Add(new OleDbParameter("@Name", word));
            if ((Int32)com.ExecuteScalar() > 0)
            {
                return true;
            }
            return false;
        }


        public static void insertClass(string eng_Name, string heb_Name, string des, string type)
        {
            com  = new OleDbCommand("INSERT INTO Classes (Eng_Name, Heb_Name,[Desc],[Type],Struct_Name) VALUES (@1,@2,@3,@4,@5)",con);
            string structName = "tb_" + eng_Name;
            com.Parameters.Add(new OleDbParameter("@1", eng_Name));
            com.Parameters.Add(new OleDbParameter("@2", heb_Name));
            com.Parameters.Add(new OleDbParameter("@3", des));
            com.Parameters.Add(new OleDbParameter("@4", type));
            com.Parameters.Add(new OleDbParameter("@5", structName));
            com.ExecuteNonQuery();


            //Create the struct table
            string firstCom = "CREATE TABLE TBNAME (Eng_Name varchar(255),Heb_Name varchar(255),[Desc] varchar(255),[Type] varchar(255))";
            string finalCom = firstCom.Replace("TBNAME", structName);
            com = new OleDbCommand(finalCom, con);
            com.ExecuteNonQuery();
        }

        public static void insertParameter(string className, string eng_Name, string heb_Name, string des, string type)
        {
            string tname = "tb_" + className;
            string firstCom = "INSERT INTO TBNAME (Eng_Name,Heb_Name,[Desc],[Type]) VALUES (@1,@2,@3,@4)";
            string finalCom = firstCom.Replace("TBNAME", tname);
            com = new OleDbCommand(finalCom, con);
            com.Parameters.Add(new OleDbParameter("@1", eng_Name));
            com.Parameters.Add(new OleDbParameter("@2", heb_Name));
            com.Parameters.Add(new OleDbParameter("@3", des));
            com.Parameters.Add(new OleDbParameter("@4", type));
            com.ExecuteNonQuery();
        }

        public static void deleteAllParm(string tbname)
        {
            string tname = "tb_" + tbname;
            string firstcom = "DELETE * FROM TBNAME";
            string finalcom = firstcom.Replace("TBNAME", tname);
            com = new OleDbCommand(finalcom, con);
            com.ExecuteNonQuery();
        }

        public static string[] GetAllClassesInDB()
        {
            com = new OleDbCommand("SELECT COUNT(Eng_Name) FROM Classes", con);
            int numOfC=(Int32)com.ExecuteScalar();
            string[] classes=new string[numOfC];
            int i = 0;
            //Insert all classes to array
            com = new OleDbCommand("SELECT Eng_Name FROM Classes", con);
            OleDbDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                classes[i] = reader.GetString(0);
                i++;
            }
            return classes;
            
            
        }

       /// <summary>
       /// This wil delete the class in the db
       /// </summary>
       /// <param name="className">te name of the class which you intened to deletee</param>
        public static void DeleteClass(string className)
        {
            /*Delete the struct table*/
            string tname = "tb_" + className;
            string command = "DROP TABLE TNAME";
            command = command.Replace("TNAME", tname);
            com = new OleDbCommand(command, con);
            com.ExecuteNonQuery();
            /***********************************/

            com = new OleDbCommand("DELETE * FROM Classes WHERE Eng_Name=@1", con);
            com.Parameters.Add(new OleDbParameter("@1", className));
            com.ExecuteNonQuery();
        }


        public static DataTable GetRuleByName(string name)
        {
            OpenConnection();

            com = new OleDbCommand("SELECT Heb_Name FROM Classes",con);
            //create an OleDbDataAdapter to execute the query
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(com);

            //create a command builder
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

            //create a DataTable to hold the query results
            DataTable dTable = new DataTable();

            //fill the DataTable
            dAdapter.Fill(dTable);

            CloseConnection();

            return dTable;
        }


        /// <summary>
        /// When rule clicked new rule row created
        /// </summary>
        /// <param name="Index"></param>
        public static void CreateNewRule(int Index)
        {
            OpenConnection();

            com = new OleDbCommand("INSERT INTO Rules(ID_Rule,Text_Rule,Contax_Rule) VALUES(@NumIndex,null,null)", con);
            com.Parameters.Add(new OleDbParameter("@NumIndex", Index));
            com.ExecuteNonQuery();

            CloseConnection();

        }

        /// <summary>
        /// Getting rules list by Name
        /// </summary>
        /// <returns></returns>
        public static string[] GetRulesList()
        {
            OpenConnection();

            com = new OleDbCommand("SELECT COUNT (ID_Rule) FROM Rules", con);
            int size = (Int32) com.ExecuteScalar(); //Getting num of records of Rules
            string[] _RulesList = new string[size];
            int Index = 0;

            com = new OleDbCommand("SELECT ID_Rule FROM Rules", con);
            OleDbDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                _RulesList[Index] = reader.GetString(0);
                Index++;
            }
    
            CloseConnection();

            return _RulesList;
        }

        /// <summary>
        /// Getting rule text in string array
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static string[] GettingRule_Text(string name)
        {

            string _RuleText="";
            OpenConnection();

            com = new OleDbCommand("SELECT Text_Rule FROM Rules WHERE ID_Rule = @ID", con);
            com.Parameters.Add(new OleDbParameter("@ID", name));
            OleDbDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    _RuleText = reader.GetString(0);
                }
                catch
                {
                    _RuleText = "empty";
                }
            }

            if (!_RuleText.Equals("empty"))
            {
                CloseConnection();
                string[] _txt = _RuleText.Split(',');  
                return _txt;
            }
            else
            {
                CloseConnection();
                string[] empty = new string[1];
                empty[0] = _RuleText;             
                return empty;
            }
        }

        /// <summary>
        /// Getting rule contax int string array
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        /*
        public static string[] GettingRule_Contax(string name)
        {


        }
        */

        public static void InsertRuleIntoDB(string name,string ruleText)
        {
            OpenConnection();

            com = new OleDbCommand("UPDATE Rules SET Text_Rule=@Txt WHERE ID_Rule=@Na", con);
            com.Parameters.Add(new OleDbParameter("@Txt", ruleText));
            com.Parameters.Add(new OleDbParameter("@Na", name));
            com.ExecuteNonQuery();

            CloseConnection();
        }


        public static void InsertRuleConIntoDB(string name, string ContText)
        {
            OpenConnection();

            com = new OleDbCommand("UPDATE Rules SET Contax_Rule=@Txt WHERE ID_Rule=@Na", con);
            com.Parameters.Add(new OleDbParameter("@Txt", ContText));
            com.Parameters.Add(new OleDbParameter("@Na", name));
            com.ExecuteNonQuery();

            CloseConnection();
        }

        public static int CountOfRules()
        {
            OpenConnection();

            com = new OleDbCommand("SELECT COUNT (ID_Rule) FROM Rules", con);
            int Sum=(Int32) com.ExecuteScalar();

            CloseConnection();

            return Sum;
        }


        public static int CountOfClasses()
        {
            OpenConnection();

            com = new OleDbCommand("SELECT COUNT (Eng_Name) FROM Classes", con);
            int Sum = (Int32)com.ExecuteScalar();

            CloseConnection();

            return Sum;
        }

        /// <summary>
        /// return list of classes in string array
        /// </summary>
        /// <returns></returns>
        public static string[] GetListOfClasses()
        {
            int index = 0;

            int ArrayLen = CountOfClasses();
            string[] _ListOFclasses = new string[ArrayLen];

            OpenConnection();
            
            com = new OleDbCommand("SELECT Heb_Name FROM Classes", con);
            OleDbDataReader reader = com.ExecuteReader();
            while (reader.Read() && index<ArrayLen)
            {
                _ListOFclasses[index] = reader.GetString(0);
                index++;
            }
            
            CloseConnection();

            return _ListOFclasses;
        }


        public static int GetNumOfPrm(string name)
        {
            string StructName = GetTBname(name);

            OpenConnection();

            string firstCommand = "SELECT COUNT (Eng_Name) FROM TBNAME";
            firstCommand = firstCommand.Replace("TBNAME", StructName);

            com = new OleDbCommand(firstCommand, con);
            int Sum = (Int32)com.ExecuteScalar();

            CloseConnection();

            return Sum;
        }

        public static string[] GetParameters(string name)
        {
            string[] _ListOfParm = new string[GetNumOfPrm(name)];
            int indexPrm = 0;
            string StructName = GetTBname(name);
            OpenConnection();

            string firstCommand = "SELECT Eng_Name FROM TBNAME";
            firstCommand = firstCommand.Replace("TBNAME", StructName);

            com = new OleDbCommand(firstCommand, con);
            OleDbDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                _ListOfParm[indexPrm] = reader.GetString(0);
                indexPrm++;
            }

            CloseConnection();

            return _ListOfParm;
        }

        public static string GetTBname(string name)
        {
            string Struct_Name="";
            
            OpenConnection();

            com = new OleDbCommand("SELECT Struct_Name FROM Classes WHERE Eng_Name=@Name", con);
            com.Parameters.Add(new OleDbParameter("@Name", name));
            OleDbDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                Struct_Name = reader.GetString(0);
            }

            CloseConnection();
            
            return Struct_Name;
        }

        public static string GetEngName_Class(string name)
        {
            string Eng_Name = "";

            OpenConnection();

            com = new OleDbCommand("SELECT Eng_Name FROM Classes WHERE Heb_Name=@Name", con);
            com.Parameters.Add(new OleDbParameter("@Name", name));
            OleDbDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                Eng_Name = reader.GetString(0);
            }

            CloseConnection();

            return Eng_Name;
        }

        public static string GetEngName_Parm(string ParmName,string TBName)
        {
            string Eng_Name = "";
            string Command="SELECT Eng_Name FROM tb_TBNAME WHERE Heb_Name=@Name";
            Command=Command.Replace("TBNAME",TBName);

            OpenConnection();

            com = new OleDbCommand(Command, con);
            com.Parameters.Add(new OleDbParameter("@Name", ParmName));
            OleDbDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                Eng_Name = reader.GetString(0);
            }

            CloseConnection();

            return Eng_Name;
        }

        public static string GettingRuleContax(string ruleName)
        {
            string contax = "";

            OpenConnection();
            //
            com = new OleDbCommand("SELECT Contax_Rule FROM Rules WHERE ID_Rule=@Name", con);
            com.Parameters.Add(new OleDbParameter("@Name", ruleName));
            OleDbDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    contax = reader.GetString(0);
                }
                catch
                {
                    contax = "empty";
                }
            }
            CloseConnection();
            return contax;
        }

        public static string[] S_getClasses()
        {
            OpenConnection();
            com = new OleDbCommand("SELECT COUNT(Eng_Name) FROM Classes", con);
            int numOfC = (Int32)com.ExecuteScalar();
            string[] classes = new string[numOfC];

            com = new OleDbCommand("SELECT Eng_Name FROM Classes", con);
            OleDbDataReader reader = com.ExecuteReader();
            int index = 0;
            while (reader.Read())
            {
                classes[index] = reader.GetString(0);
                index++;
            }

            CloseConnection();

            return classes;
        }

        public static string[] S_getPrms(string cls)
        {
            OpenConnection();
            string command = "SELECT COUNT(Eng_Name) FROM TBNAME";
            command = command.Replace("TBNAME", "tb_" + cls);
            com = new OleDbCommand(command, con);
            int numOfC = (Int32)com.ExecuteScalar();
            string[] prms = new string[numOfC];

            string prmsCommand = "SELECT Eng_Name FROM tb_Ship";
            //prmsCommand = prmsCommand.Replace("TBNAME", "tb_" + cls);

            com = new OleDbCommand(prmsCommand, con);
            OleDbDataReader reader = com.ExecuteReader();
            int index = 0;
            while (reader.Read())
            {
              prms[index] = reader.GetString(0);
              index++;
            }

            return prms;
        }

    }
}
